package com.moleq.network;

import java.security.interfaces.DSAKey;
import java.text.DateFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.moleq.db.DatabaseHelper;
import com.moleq.db.DatabaseSchema;
import com.moleq.util.MoleQUtil;
import com.moleq.util.StringUtils;

import android.R.integer;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;


public class InventoryReportCreator {
	private DatabaseHelper DBHelper;
	private DatabaseHelper PosDBHelper;

	public InventoryReportCreator() {
	}
	
	public InventoryReportCreator(Context context,Context context2){
		this.DBHelper = new DatabaseHelper(context,DatabaseHelper.STOCKDB_NAME);
		PosDBHelper = new DatabaseHelper(context2,DatabaseHelper.DB_NAME);
	}

	public String generateReport(String... args) {
		String strRpt = "";
		String rptName = args[0];
		String station = "";
		String fromDate = args[1];
		String toDate = args[2];
		String upc = "";
		// String rptName = Constant.RPT_FIXED_TOTAL;
		// String fromDate = "20120922";
		// String toDate = "20121022";
		if (rptName.equals(Constant.RECEIVING_INQUIRY)) {
//			strRpt = getInventoryRptByDate(upc);
		}else{
			/////////
		}
		// strRpt = "New Report (" + rptName + ", from date:" + fromDate +
		// " to date:" + toDate + ")";

		return strRpt;
	}
	
	public String getReceivingInquiry(String upc,String startDate,String endDate,boolean showAmt) {
		String sql = "select ir_id,ir_upc,ir_qty*ir_qty_per_case,CAST(ir_rate AS FLOAT)/ir_qty_per_case,ir_date " +
					"from t_item_receive where 1=1";
		if (!upc.trim().equals("")) {
			sql += " and ir_upc = '"+ upc+"'";
		}
		if (!startDate.trim().equals("") && !endDate.trim().equals("")) {
			sql += " and ir_date >= '"+startDate +"' and ir_date <= '"+endDate+"'";
		}
		sql += " order by ir_date desc,ir_id desc";
		
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		String result = "";
		if (cursor == null)
			return result;
		while (cursor.moveToNext()) {
			int ir_id = cursor.getInt(0);
			String sPLU = cursor.getString(01);
			String itemName = "";
				itemName = getItemName(sPLU);
			int ir_qty = cursor.getInt(2);
			String ir_rate = " ";
			if (showAmt) {
				ir_rate = MoleQUtil.fixNumberWithDigits(cursor.getDouble(3)) ;
			}
			String ir_date = MoleQUtil.fixDateTime2(cursor.getString(4), "yyyy-MM-dd", "yyyyMMdd");
			result += "|(" + String.valueOf(ir_id) + ","
					+ CheckSign(CheckNull(sPLU+"  " +ir_date))+ ","
					+ String.valueOf(ir_qty) + "," + ir_rate + ")";
				result += "|(" +String.valueOf(ir_id+"-A")
						+ "," + CheckSign(CheckNull(itemName));
				result += ", , )";

			}
		cursor.close();
		if (result.length() > 0) {
			return Constant.RECEIVING_INQUIRY + result;
		} else {
			return result;
		}
	}
	
	public String getInventoryReceiving(String upc) {
		String sql = "select ir_id,ir_upc,ir_qty*ir_qty_per_case,CAST(ir_rate AS FLOAT)/ir_qty_per_case,ir_date " +
					"from t_item_receive where 1=1";
		if (!upc.trim().equals("")) {
			sql += " and ir_upc = '"+ upc+"'";
		}
		sql += " order by ir_date desc,ir_id desc";
		
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		String result = "";
		if (cursor == null)
			return result;
		while (cursor.moveToNext()) {
			int ir_id = cursor.getInt(0);
			String sPLU = cursor.getString(01);
//			String itemName = "";
//			itemName = getItemName(sPLU);
			int ir_qty = cursor.getInt(2);
			String ir_date = MoleQUtil.fixDateTime2(cursor.getString(4), "yyyy-MM-dd", "yyyyMMdd");
			result += "|(" + String.valueOf(ir_id) + ","
					+ CheckSign(CheckNull("  "+ir_date))+ ","
					+ String.valueOf(ir_qty) + ", " + ")";

			}
		cursor.close();
		if (result.length() > 0) {
			return Constant.RECEIVING_INQUIRY + result;
		} else {
			return result;
		}
	}
	
	public String getInventoryRptByUpc(String upc) {
		String sql = "select ir_upc,sum(ir_qty*ir_qty_per_case) " +
					"from t_item_receive where ir_upc = '"+ upc+"'";
				sql += " group by ir_upc order by ir_upc";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		String result = "";
		if (cursor == null)
			return result;
		while (cursor.moveToNext()) {
			String sPLU = cursor.getString(0);
			String itemName = "";
			itemName = getItemName(sPLU);
			int ir_qty = cursor.getInt(1);

			result += "|(" + CheckSign(CheckNull(sPLU)) + ","
					+ CheckSign(CheckNull(sPLU))+ ","
					+String.valueOf(ir_qty)+", " + ")";
			
			result += "|(" +CheckSign(CheckNull(sPLU+"-A"))
					+ "," + CheckSign(CheckNull(itemName));
			result += ", , )";
		}
		cursor.close();
		
		sql = "select is_upc_plu,sum(is_qty*-1) " +
				"from t_item_sales where is_upc_plu = '"+ upc+"'";
			sql += " group by is_upc_plu ";
			db = DBHelper.getWritableDatabase();
			Cursor cursor2 = db.rawQuery(sql, null);
			if (cursor2 != null){
//				return result;
				while (cursor2.moveToNext()) {
					
					String sPLU = cursor2.getString(0);
//					String itemName = "";
//					itemName = getItemName(sPLU);
					int ir_qty = cursor2.getInt(1);
					
					result += "|(" + CheckSign(CheckNull(sPLU)) + ","
							+ CheckSign(CheckNull(sPLU))+ ","
							+ String.valueOf(ir_qty) + ", " + ")";
				}
			}
		cursor2.close();
		
		if (result.length() > 0) {
			return Constant.INVENTORY + result;
		} else {
			return result;
		}
	}
	
	public String getInventoryRptDetailByUpc(String upc) {
		String sql="";
		String result = "";
		sql = "select is_upc_plu,sum(is_qty*-1),is_date " +
				"from t_item_sales where is_upc_plu = '"+ upc+"' group by is_date order by is_date desc";
		
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		db = DBHelper.getWritableDatabase();
		Cursor cursor2 = db.rawQuery(sql, null);
		if (cursor2 != null){
//				return result;
			while (cursor2.moveToNext()) {
				
//				String sPLU = cursor2.getString(0);
				int ir_qty = cursor2.getInt(1);
				String is_date = MoleQUtil.fixDateTime2(cursor2.getString(2), "yyyy-MM-dd", "yyyyMMdd");
				result += "|(" + CheckSign(CheckNull(is_date)) + ","
						+ CheckSign(CheckNull("  "+is_date))+ ","+
						ir_qty+ ", "  + ")";
			}
		}
		cursor2.close();
		
		if (result.length() > 0) {
			return Constant.INVENTORY + result;
		} else {
			return result;
		}
	}

	public String getItemDiscount(String PLU) {
		String sql = "select ds_desc from t_discount_setting where ds_no = "
				+ PLU;
		SQLiteDatabase db = PosDBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return "";
		if (cursor.moveToNext()) {
			String name = cursor.getString(0);
			cursor.close();
			return name;
		}
		return "";
	}
//
	public String getItemName(String PLU) {
		String sql = "select F02 from t_item_setting where F01 = '" + PLU + "'";
		SQLiteDatabase db = PosDBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return "";
		if (cursor.moveToNext()) {
			String name = cursor.getString(0);
			cursor.close();
			return name;
		}
		cursor.close();
		return "";
	}
//
	public String CheckSign(String str) {
		if (str == null)
			return null;
		String data = str.substring(1, (str.length() - 1));
		String result = "";
		for (int i = 0; i < data.length(); i++) {
			String charString = String.valueOf(data.charAt(i));
			if (charString.equals("'")) {
				result += charString + "'";
			} else {
				result += charString;
			}
		}

		return "'" + result + "'";
	}

	public String CheckNull(String value) {
		if (value == null) {
			return null;
		} else {
			return "'" + value + "'";
		}
	}
}
